package com.mmy.dao.visa;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.visa.TVisaOrder;

/**
 * 类的介绍：dao层 签证订单信息
 *
 * @author hanxl
 * @date 2020-07-23
 * @version 1.0
 */

public interface TVisaOrderRepository extends JpaRepository<TVisaOrder, Integer>, JpaSpecificationExecutor<TVisaOrder> {

	/**
	 * 获取最大流水号
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "SELECT MAX(iID)  FROM tVisaOrder", nativeQuery = true)
	Object getMaxID();

	/**
	 * 获得订单最大编码
	 *
	 * @author 创建人dingxd
	 * @date 修改日期 2020.11.17
	 */
	@Query(value = "SELECT MAX(cOrderID) FROM tVisaOrder WHERE LEFT(cOrderID,16) = CONCAT(:iPlanID,REPLACE(DATE(NOW()),'-',''))", nativeQuery = true)
	Object getMaxCode(@Param("iPlanID") String strPlanID);

	/* 获得预留订单 */
	@Query(value = "select * from tVisaOrder where iOrderState=0", nativeQuery = true)
	List<TVisaOrder> findByOrderState0();

	/* 获得取消预留订单 */
	@Query(value = "select * from tVisaOrder where iOrderState=3", nativeQuery = true)
	List<TVisaOrder> findByOrderState3();

	/* 搜索预约单 状态为0 */
	@Query(value = "select * from tVisaOrder where iOrderState=0 and cReserveName like %?1%", nativeQuery = true)
	List<TVisaOrder> findByName(String reserveName);

	/*
	 * 新增id
	 */

	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tVisaOrder(iID, cOrderID, iReserveUserID, dReserveDate, cReserveName, cReserveTel, iOrderUserID, dOrderDate, cOrderName, cOrderTel, iOrderState, fOrderAmount, fReceivable, fReceipt, fFloor, fPeer, fPrice, iPayType, cVoucher, iCashierState, dCashierDate, iCashierID, iAccountState, dAccountDate, iAccountID, iInvoiceState, fRefund, iRefundState, iRefundProcess, bRemittanceState, iProcess, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:id, NULL, 0, NULL,NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 0, NULL, 0, 0, NULL, 0, 0, b'0', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("id") int id);

	/**
	 * 根据订单ID查询
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tVisaOrder where iID = :iID", nativeQuery = true)
	TVisaOrder findByID(@Param("iID") int iID);

	/**
	 * 
	 * 根据ID查询订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.02.24
	 */
	@Query(value = "select *  from tVisaOrder where left (cOrderID,8) = :iVisaID", nativeQuery = true)
	List<TVisaOrder> findByVisaID(@Param("iVisaID") int iVisaID);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tVisaOrder where tVisaOrder.iID in (:lstID)", nativeQuery = true)
	void delVisaOrder(@Param("lstID") List<Integer> lstID);

	/*
	 * 根据出纳审核状态查找
	 */
	@Query(value = "select *  from tVisaOrder where iCashierState = :strCashierState and left (cOrderID,9) = :iVisaID", nativeQuery = true)
	List<TVisaOrder> findByCashierAndVisaID(@Param("strCashierState") String strCashierState,
			@Param("iVisaID") int iVisaID);

	/*
	 * 根据会计审核状态查找
	 */
	@Query(value = "select *  from tVisaOrder where iAccountState = :strAccountState and left (cOrderID,9) = :iVisaID", nativeQuery = true)
	List<TVisaOrder> findByAccountAndVisaID(@Param("strAccountState") String strAccountState,
			@Param("iVisaID") int iVisaID);

	/*
	 * 查询订单状态为2和4的订单
	 */
	@Query(value = "select *  from tVisaOrder where iOrderState in(2,4)", nativeQuery = true)

	List<TVisaOrder> findByOrderState24();

	/**
	 * 个人中心通过登录人获取签证预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tVisaOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<TVisaOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取签证订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tVisaOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TVisaOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取签证退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tVisaOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TVisaOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取签证预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tVisaOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取签证退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tVisaOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取签证订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tVisaOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

}
